function rslt = get_info_from_database( mode, varargin)
% This function returns:
% - security_id: if the input is a short_name, mode 'sec-id'
% - name and short_name: if the input is a security_id, mode 'name'
% - trading_destination_id: if the input is a name/short name
% - data of one security with a given security_id, a give begin and end
% date
%
% Example:
% META:                 =========================
% rslt = get_info_from_database('column', 'trading_daily_2013'): return the column name
% rslt = et_info_from_database('sql','COMMIT');
% rslt = et_info_from_database('custom','<table>', ['<name_column>'], '[value_column]','<order>');
% rslt = et_info_from_database('sql','<sql query>');
%
% SECURITY INFO:        =========================
% rslt = get_info_from_database('security-info', 4)
% rslt = get_info_from_database('sec_id', 'AAA')    : return the security ID
% rslt = get_info_from_database('name', 1)          : return the name of security
% rslt = get_info_from_database('ticker', 1)        : return the ticker of security
% rslt = get_info_from_database('ticksize', 1)      : 
% 
% rslt = get_info_from_database('trading_destination', 1)
% rslt = get_info_from_database('trading_destination', 'AAA')
% rslt = get_info_from_database('trading_destination_id', 1)
% rslt = get_info_from_database('trading_destination_id', 'AAA')
%
% rslt = get_info_from_database('member', 'HSE')
% rslt = get_info_from_database('member', 'all')
% rslt = get_info_from_database('member', 'all-index')
%
% STOCK PRICES:         =========================
% rslt = get_info_from_database('security', 'sec_id', 1, 'from', '01/01/2009', 'to', '31/12/2011')
% rslt = get_info_from_database('security', 'ticker', '^VNINDEX', 'from', '01/01/2009', 'to', '31/12/2011')
% rslt = get_info_from_database('last_price', 'sec_id', 1)
% rslt = get_info_from_database('quarter_price', 'sec_id', 1)
% rslt = get_info_from_database('prices_in_quarter', 1, 2012, 2)
% rslt = get_info_from_database('last_n_lines', 'sec_id', 1,'num_record',1000)
% rslt = get_info_from_database('security_idx', 'sec_id1', 1, 'sec_id2', 2,'num-record',1000)
% rslt = get_info_from_database('intraday', 'sec_id', 1, 'from', '01/01/2011', 'to', '31/12/2011')
% 
% SECTORS:              =========================
% rslt = get_info_from_database('sector', 4)
% rslt = get_info_from_database('sector-info', 'all')
% rslt = get_info_from_database('sector-info', 6)
% rslt = get_info_from_database('security-sector', 6)
% rslt = get_info_from_database('security-sector', 'banks')
%
% FUNDAMENTAL RECORDS   =========================
% rslt = get_info_from_database('floating_share', security_id, year, quarter)
% rslt = get_info_from_database('floating_share', security_id)
%
% rslt = get_info_from_database('financial_indicators',sec_id, yr, quarter);
% rslt = get_info_from_database('financial_indicators',sec_id,'from', yr1, 'to',yr2);
% rslt = get_info_from_database('financial_indicators',sec_id,'from', '20121', 'to','20122');
% rslt = get_info_from_database('financial_indicator_stox',sec_id, yr, quarter);
% rslt = get_info_from_database('financial_indicator_bank_stox',sec_id, yr, quarter);
%
% rslt = get_info_from_database('balance_sheet_stox',sec_id, yr, quarter);
% rslt = get_info_from_database('balance_sheet_bank_stox',sec_id, yr, quarter);
% rslt = get_info_from_database('income_statement_stox',sec_id, yr, quarter);
% rslt = get_info_from_database('income_statement_bank_stox',sec_id, yr, quarter);
%
% OTHER STATS           =========================
% rslt = get_info_from_database('daily_stats', 'sec_id', 1)
% rslt = get_info_from_database('liquidity_measures', 'sec_id', 1, 'from', '01/01/2011', 'to', '31/12/2011')
% rslt = get_info_from_database('market_impact_model', 4)
%
% UN-USED               =========================
% rslt = get_info_from_database('capital', 4)
% rslt = get_info_from_database('floating', 4)
% rslt = get_info_from_database('financial-report-item-id', 'current assets')
% rslt = get_info_from_database('sector-cp68', 4) 					
% rslt = get_info_from_database('sector-info-cp68', 'all')			
% rslt = get_info_from_database('sector-info-cp68', 6)				
% rslt = get_info_from_database('sector-security-cp68', 6)			: get sector_id of specific security
% rslt = get_info_from_database('security-sector-cp68', 6)         
% rslt = get_info_from_database('security-sector-cp68', 'banks')	%UPDATED
% rslt = get_info_from_database('financial_ratio', 'sec_id',sec_id, year, quarter)
% rslt = get_info_from_database('financial_ratio', 'sec_id',sec_id,'from', year1,'to',year2)
% rslt = et_info_from_database('balance_sheet_cp68',sec_id, yr, quarter);
% rslt = et_info_from_database('balance_sheet_cp68',sec_id, yr);  %total year
% rslt = et_info_from_database('income_statement_cp68',sec_id, yr, quarter);

persistent dbConn
rslt = [];

if isempty(dbConn)
    dbConn = createdbConn;
end

if(~isempty(dbConn.Message))
    fprintf('Error in connecting to database: %s \n', dbConn.Message );
    return;
end

switch lower(mode)
    
    case 'column'
        table = varargin{1};
        sql = ['SELECT `COLUMN_NAME` '...
            'FROM `INFORMATION_SCHEMA`.`COLUMNS` '...
            'WHERE `TABLE_SCHEMA`=''%s'' '...
            'AND `TABLE_NAME`=''%s''' ];
        req_sql = sprintf(sql, dbConn.Instance, table);
        curs = fetch(exec(dbConn, req_sql));
        if(rows(curs))
            rslt = get(curs, 'Data');
        end
        
    case 'security-info'
        sql = [ 'SELECT s.*, sc.eng_name as sector_ename, '...
            'td.short_name as trading_destination_name '...
            'FROM ' ...
            '    `security` s '...
            'LEFT JOIN `sector` sc ON (s.sector_id = sc.sector_id) '...
            'LEFT JOIN `trading_destination` td ON ( '...
            '    s.trading_destination_id = td.trading_destination_id '...
            ') WHERE status = 1 AND security_id=''%d'''];
        req_sql = sprintf(sql,varargin{1});
        
        colnames = {'security_id','sector_id','eng_name','short_name','viet_name',...
            'trading_destination_id','security_type','price_scale_id',...
            'floating_shares','posted_shares','capital','foreign_owned_share',...
            'status','sec_ename','trading_destination_name' %extra
            };
        
        curs = exec(dbConn, req_sql);
        temp_rslt = get(fetch(curs), 'Data');
        if(size(temp_rslt,2)~=1)
            rslt = struct();
            for c=1:numel(colnames)
                %                 rslt = setfield(rslt, colnames{c}, temp_rslt{:,c});
                rslt.(colnames{c}) = temp_rslt{:,c};
            end
        else
            rslt = temp_rslt;
        end
        
        % sec_id
    case 'sec_id'
        req_sql = sprintf(['SELECT security_id FROM security ' ...
            'WHERE short_name LIKE ''%s'''], varargin{1});
        curs = exec(dbConn, req_sql);
        rslt = get(fetch(curs), 'Data');
        rslt = rslt{1};
        
        % name and short_name
    case 'name'
        req_sql = sprintf(['SELECT viet_name, eng_name, short_name FROM security ' ...
            'WHERE security_id = %d'], varargin{1});
        curs = exec(dbConn, req_sql);
        rslt = get(fetch(curs), 'Data');
        
        % ticker
    case 'ticker'
        req_sql = sprintf(['SELECT short_name FROM security ' ...
            'WHERE security_id = %d'], varargin{1});
        curs = exec(dbConn, req_sql);
        rslt = get(fetch(curs), 'Data');
        rslt = rslt{1};
        
        % ticksize
    case 'ticksize'
        req_sql = sprintf(['SELECT threshold, tick_size '...
            'FROM price_scale '...
            'WHERE price_scale_id = (SELECT price_scale_id '...
            'FROM security WHERE security_id = %d)'], varargin{1});
        curs = exec(dbConn, req_sql);
        rslt = get(fetch(curs), 'Data');
        rslt = cell2mat(rslt);
        
        % trading_destination id
    case 'trading_destination_id'
        if isscalar(varargin{1})
            req_sql = sprintf(['SELECT trading_destination_id FROM security ' ...
                'WHERE security_id = %d'], varargin{1});
        else
            req_sql = sprintf(['SELECT trading_destination_id FROM security ' ...
                'WHERE short_name LIKE ''%s'''], varargin{1});
        end
        curs = exec(dbConn, req_sql);
        rslt = get(fetch(curs), 'Data');
        rslt = rslt{1};
        
        % trading_destination
    case 'trading_destination'
        if isscalar(varargin{1})
            req_sql = sprintf(['SELECT short_name '....
                'FROM trading_destination '...
                'WHERE trading_destination_id IN '...
                '(SELECT trading_destination_id FROM security ' ...
                'WHERE security_id = %d)'], varargin{1});
        else
            req_sql = sprintf(['SELECT short_name '....
                'FROM trading_destination '...
                'WHERE trading_destination_id IN '...
                '(SELECT trading_destination_id FROM security ' ...
                'WHERE short_name LIKE ''%s'')'], varargin{1});
        end
        curs = exec(dbConn, req_sql);
        rslt = get(fetch(curs), 'Data');
        rslt = rslt{1};
        
    case 'member'
        switch varargin{1}
            case 'all'
                req_sql = ['SELECT security_id '...
                    'FROM security '...
                    'WHERE security_type = 1 AND `status`=1'];
            case 'all-index'
                req_sql = ['SELECT security_id '...
                    'FROM security '...
                    'WHERE security_type = 2'];
            otherwise
                req_sql = sprintf(['SELECT security_id '...
                    'FROM security '...
                    'WHERE `status`=1 AND security_type = 1 AND trading_destination_id IN '...
                    '(SELECT trading_destination_id '...
                    'FROM trading_destination '...
                    'WHERE short_name LIKE ''%s'')'], varargin{1});
                
                
        end
        curs = exec(dbConn, req_sql);
        rslt = get(fetch(curs), 'Data');
        rslt = cell2mat(rslt);
        % data
    case 'security'
        
        date_from_val = datenum(varargin{4}, 'dd/mm/yyyy');
        date_to_val   = datenum(varargin{6}, 'dd/mm/yyyy');
        
        year_from = year(date_from_val);
        year_to   = year(date_to_val);
        
        end_of_year = arrayfun(@(y)eomdate(y,12), year_from:year_to);
        nb_years = numel(end_of_year);
        if nb_years == 1
            date_val = [date_from_val;date_to_val];
        else
            date_val = NaN(2*nb_years,1);
            date_val(1:2:end) = [date_from_val end_of_year(1:end-1)+1];
            date_val(2:2:end) = [end_of_year(1:end-1)+1 date_to_val];
        end
        if isnumeric(varargin{2})
            % security id
            sec_id = varargin{2};
        else
            % ticker to security id
            rslt = get_info_from_database('sec_id', varargin{2});
            if strcmp(rslt, 'No Data')
                return
            else
                sec_id = rslt;
            end
        end
        rslt = [];
        for y=1:nb_years
            table_name = sprintf('trading_daily_%d',year_from+y-1);
            date_from = datestr(date_val(2*y-1), 'yyyy-mm-dd');
            date_to   = datestr(date_val(2*y), 'yyyy-mm-dd');
            
            req_sql = sprintf(['SELECT date, volume, open_prc, high_prc, low_prc, close_prc ', ...
                sprintf('FROM %s ', table_name), ...
                'WHERE security_id = %d ', ...
                'AND date BETWEEN ''%s'' and ''%s'''], sec_id, date_from, date_to);
            curs = exec(dbConn, req_sql);
            temp_rslt = get(fetch(curs), 'Data');
            if ~strcmpi(temp_rslt{1}, 'no data')
                rslt = vertcat(rslt,horzcat(datenum(temp_rslt(:,1), 'yyyy-mm-dd'), cell2mat(temp_rslt(:,2:end))));
            end
        end
        
    case 'last_price'
        yr = year(now);
        rslt = '';
        for i = yr:-1:2000
            table_name = sprintf('trading_daily_%d', i);
            req_sql = sprintf(['SELECT date, close_prc '...
                'FROM %s JOIN (', ...
                'SELECT security_id sec_id, max(date) as maxdate '...
                'FROM %s ' ...
                'GROUP BY security_id) as x ' ...
                'ON %s.security_id = sec_id AND date = maxdate ' ...
                'WHERE security_id = %d'], table_name, table_name, table_name, varargin{2});
            curs = exec(dbConn, req_sql);
            rslt = get(fetch(curs), 'Data');
            if(size(rslt,2)==2)
                break;
            end
        end
        
    case 'quarter_price'
        %get_info_from_database('quarter_price',security_id, year, quarter);
        if (varargin{3} == 1)
            mon = 3;
        elseif (varargin{3} == 2)
            mon = 6;
        elseif (varargin{3} == 3)
            mon = 9;
        elseif (varargin{3} == 4)
            mon = 12;
        end
        req_sql = sprintf(['select close_prc from trading_daily_%d '...
            'where security_id = %d and `date` = '...
            '(select max(`date`) from trading_daily_%d '
            ' where month(date)= %d and security_id = %d )'],...
            varargin{2},varargin{1},varargin{2},mon,varargin{1});
        curs = exec(dbConn, req_sql);
        rslt = get(fetch(curs), 'Data');       
    case 'prices_in_quarter'
        security_id = varargin{1};
        yr = varargin{2};
        quarter = varargin{3};
        
        req_sql = sprintf(['select date, volume, open_prc, high_prc, '...
            'low_prc, close_prc from trading_daily_%d '...
            'where security_id = %d AND month(`date`) <=  (%d*3) '...
            'AND month(`date`)> (%d-1)*3 ORDER BY `date` ASC '],...
            yr, security_id,quarter,quarter);
        curs = exec(dbConn, req_sql);
        rslt = get(fetch(curs), 'Data');   
        
        
    case 'last_n_lines'
        req_sql = sprintf(['SELECT date, volume, open_prc, high_prc, low_prc, close_prc FROM '...
            sprintf('(SELECT * FROM trading_daily_%d ', year(now)) ...
            'WHERE security_id = %d ORDER BY date DESC) as x '...
            'LIMIT %d'], varargin{2}, varargin{4});
        curs = exec(dbConn, req_sql);
        rslt = get(fetch(curs), 'Data');
        if ~strcmpi(rslt{1}, 'no data')
            rslt = horzcat(datenum(rslt(:,1), 'yyyy-mm-dd'), cell2mat(rslt(:,2:end)));
        end
        % sector
    case 'security_idx'
        table_name = sprintf('trading_daily_%d', year(now));
        req_sql = sprintf(['SELECT j.date, j.stock, j.ind FROM '...
            '(SELECT td1.date date, td1.close_prc stock, td2.close_prc ind '...
            'FROM %s td1, %s td2 '...
            'WHERE td1.security_id = %d and td2.security_id = %d '...
            'AND td1.date = td2.date ORDER BY td1.date DESC) as j LIMIT %d'],...
            table_name, table_name, varargin{2}, varargin{4}, varargin{6});
        curs = exec(dbConn, req_sql);
        rslt = get(fetch(curs), 'Data');
        if ~strcmpi(rslt{1}, 'no data')
            rslt = horzcat(datenum(rslt(:,1), 'yyyy-mm-dd'), cell2mat(rslt(:,2:end)));
        end    
        
    
    case 'sector'
        security_id = varargin{1};
        if ischar(security_id)
            security_id = get_info_from_database('sec_id', security_id);
            if ischar(security_id)
                fprintf('get_info_from_database:mode <%s>: cannot find security_id of %s',...
                    mode, varargin{1});
                return
            end
        end
        req_sql = sprintf(['SELECT eng_name '...
            'FROM sector '...
            'WHERE sector_id IN '...
            '(SELECT sector_id FROM security WHERE security_id = %d)'], security_id);
        curs = exec(dbConn, req_sql);
        rslt = get(fetch(curs), 'Data');
        rslt = rslt{1};
        
        % security_id from sector
    case 'security-sector'
        sector_id = varargin{1};
        if ischar(sector_id)
            sector_id = get_info_from_database('sector-info', sector_id);
        end
        req_sql = sprintf(['SELECT security_id '...
            'FROM security '...
            'WHERE sector_id = %d'], sector_id);
        curs = exec(dbConn, req_sql);
        rslt = get(fetch(curs), 'Data');
        rslt = cell2mat(rslt);
        
    case 'sector-info'
        sub_mode = varargin{1};
        if ischar(sub_mode)
            switch lower(sub_mode)
                case 'all'
                    req_sql = 'SELECT sector_id, eng_name FROM sector';
                otherwise
                    req_sql = sprintf(['SELECT sector_id '...
                        'FROM sector '...
                        'WHERE eng_name LIKE ''%s'''], sub_mode);
            end
        else
            req_sql = sprintf('SELECT sector_id, eng_name FROM sector WHERE sector_id = %d', sub_mode);
        end
        curs = exec(dbConn, req_sql);
        rslt = get(fetch(curs), 'Data');
        if size(rslt, 2) == 1
            rslt = rslt{1};
        end
        
        %update new sector
    case 'sector-cp68'
        security_id = varargin{1};
        if ischar(security_id)
            security_id = get_info_from_database('sec_id', security_id);
            if ischar(security_id)
                fprintf('get_info_from_database:mode <%s>: cannot find security_id of %s',...
                    mode, varargin{1});
                return
            end
        end
        req_sql = sprintf(['SELECT eng_name '...
            'FROM sector_2 '...
            'WHERE sector_id IN '...
            '(SELECT sector_id FROM security WHERE security_id = %d)'], security_id);
        curs = exec(dbConn, req_sql);
        rslt = get(fetch(curs), 'Data');
        rslt = rslt{1};
        
    case 'sector-security-cp68'
        
        sector_id = varargin{1};
        if ischar(sector_id)
            sector_id = get_info_from_database('sector-info-2', sector_id);
        end
        req_sql = sprintf(['select s.sector_id ' ...
            'FROM sector_security ss, sector_2 s ' ...
            'where s.sector_id=ss.sector_id and security_id=%d'], sector_id);
        curs = exec(dbConn, req_sql);
        rslt = get(fetch(curs), 'Data');
        rslt = cell2mat(rslt);
        
    case 'security-sector-cp68'
        sector_id = varargin{1};
        if ischar(sector_id)
            sector_id = get_info_from_database('sector-info-2', sector_id);
        end
        req_sql = sprintf(['select DISTINCT security_id ' ...
            'FROM sector_security ' ...
            'WHERE sector_id=%d'], sector_id);
        curs = exec(dbConn, req_sql);
        rslt = get(fetch(curs), 'Data');
        rslt = cell2mat(rslt);
        
    case 'sector-info-cp68'
        sub_mode = varargin{1};
        if ischar(sub_mode)
            switch lower(sub_mode)
                case 'all'
                    req_sql = 'SELECT sector_id, eng_name FROM sector_2';
                otherwise
                    req_sql = sprintf(['SELECT sector_id '...
                        'FROM sector_2 '...
                        'WHERE eng_name LIKE ''%s'''], sub_mode);
            end
        else
            req_sql = sprintf('SELECT sector_id, eng_name FROM sector_2 WHERE sector_id = %d', sub_mode);
        end
        curs = exec(dbConn, req_sql);
        rslt = get(fetch(curs), 'Data');
        if size(rslt, 2) == 1
            rslt = rslt{1};
        end
        
        % financial ratio
        % rslt = get_info_from_database('financial_ratio', sec_id, yr, quarter)
    case 'financial_ratio'
        security_id = varargin{2};
        if(isscalar(varargin{3}))
            yr = varargin{3};
            quarter = varargin{4};
            
            req_sql = sprintf('SELECT * FROM `financial_ratio_fpts` WHERE `security_id` = %d AND quarter = %d AND `year`=%d',security_id,quarter,yr);
        else
            year1 = varargin{4};
            year2 = varargin{6};
            req_sql = sprintf(['SELECT * FROM `financial_ratio_fpts` WHERE `security_id` = %d AND `year` >= %d AND `year`<=%d ' ...
                'order by `year` ASC, `quarter` ASC'],security_id,year1,year2);
        end
        curs = exec(dbConn, req_sql);
        rslt = get(fetch(curs), 'Data');
        
    case 'floating_share'
        security_id = varargin{1};
        if(nargin==2)
            yr = year(now);
            qr = floor(month(now) / 3)+1;
        else
            yr = varargin{2};
            qr = varargin{3};
        end
        
        yquarter = yr*10+qr;
        table = 'balance_sheet_stox';
        security = get_info_from_database('security-info',security_id);
        if(security.sector_id == 12) %bank
            table = 'balance_sheet_bank_stox';
        end
        
        sql = ['select `investment_capital_owners_equity`*100 '...
            'from `%3$s` where security_id= %1$d and '...
            '`investment_capital_owners_equity` is not null '...
            ' and (`year`*10+`quarter`) - %2$d = (' ...
            'select max(distance) from ' ...
            '(select (`year`*10+`quarter`) as `yquarter`,'...
            '`investment_capital_owners_equity`,(`year`*10+`quarter`)-%2$d as distance'...
            ' from `%3$s` where security_id= %1$d '...
            ' and `investment_capital_owners_equity` is not null '...
            ' HAVING distance <= 0) as dt)'];
        
        req_sql = sprintf(sql,security_id,yquarter,table);
        curs = exec(dbConn, req_sql);
        rslt = get(fetch(curs), 'Data');
        if strcmpi(rslt{1}, 'no data')
            rslt = 0;
        else
            rslt = rslt{1};
        end    
    case 'floating'
        security_id = varargin{1};
        if ischar(security_id)
            security_id = get_info_from_database('sec_id', security_id);
            if ischar(security_id)
                fprintf('get_info_from_database:mode <%s>: cannot find security_id of %s',...
                    mode, varargin{1});
                return
            end
        end
        req_sql = sprintf('SELECT floating_shares FROM security WHERE security_id = %d', security_id);
        curs = exec(dbConn, req_sql);
        rslt = get(fetch(curs), 'Data');
        rslt = rslt{1};
        
    
        
    
        
        
    case 'market_impact_model'
        req_sql = sprintf(['SELECT const, kappa, gamma, beta FROM market_impact_model '...
            'WHERE security_id = %d'], varargin{1});
        curs = exec(dbConn, req_sql);
        rslt = get(fetch(curs), 'Data');
        if ~strcmpi(rslt{1}, 'no data')
            rslt = cell2mat(rslt);
        else
            rslt = [];
        end
        
    case 'intraday'
        date_from_val = datenum(varargin{4}, 'dd/mm/yyyy');
        date_to_val   = datenum(varargin{6}, 'dd/mm/yyyy');
        if date_from_val == date_to_val
            date_to_val = date_to_val+1;
        end
        year_from = year(date_from_val);
        year_to   = year(date_to_val);
        
        end_of_year = arrayfun(@(y)eomdate(y,12), year_from:year_to);
        nb_years = numel(end_of_year);
        if nb_years == 1
            date_val = [date_from_val;date_to_val];
        else
            date_val = NaN(2*nb_years,1);
            date_val(1:2:end) = [date_from_val end_of_year(1:end-1)+1];
            date_val(2:2:end) = [end_of_year(1:end-1)+1 date_to_val];
        end
        rslt = [];
        for y=1:nb_years
            table_name = sprintf('intraday_%d',year_from+y-1);
            date_from = datestr(date_val(2*y-1), 'yyyy-mm-dd');
            date_to   = datestr(date_val(2*y), 'yyyy-mm-dd');
            
            req_sql = sprintf(['SELECT date, volume, price, bid, bid_size, ask, ask_size, sell ', ...
                sprintf('FROM %s ', table_name), ...
                'WHERE security_id = %d ', ...
                'AND date BETWEEN ''%s'' and ''%s'''], varargin{2}, date_from, date_to);
            curs = exec(dbConn, req_sql);
            temp_rslt = get(fetch(curs), 'Data');
            if ~(isnumeric(temp_rslt) || strcmpi(temp_rslt{1}, 'no data'))
                dates = cellfun(@(c)datenum(c(1:19), 'yyyy-mm-dd HH:MM:SS'), temp_rslt(:,1));
                rslt = vertcat(rslt,horzcat(dates, cell2mat(temp_rslt(:,2:end))));
            end
        end
        if ~isempty(rslt)
            rslt = struct('date', rslt(:,1),...
                'volume', rslt(:,2),...
                'price', rslt(:,3),...
                'bid', rslt(:,4),...
                'bid_size', rslt(:,5),...
                'ask', rslt(:,6),...
                'ask_size', rslt(:,7),...
                'sell', rslt(:,8));
        end
        
    case 'daily_stats'
        rslt = [];
        req_sql = sprintf(['SELECT mean_ret, std_ret, median_ret, skew_ret, kurtosis_ret, GK_vol, max_dd, ', ...
            'min_turnover_pct, med_turnover_pct, max_turnover_pct,', ...
            'min_turnover_cash, med_turnover_cash, max_turnover_cash,', ...
            'alpha, beta, tracking_error ',...
            'FROM daily_stats ', ...
            'WHERE security_id = %d '], varargin{2});
        curs = exec(dbConn, req_sql);
        temp_rslt = get(fetch(curs), 'Data');
        if ~strcmpi(temp_rslt{1}, 'no data')
            temp_rslt = cell2mat(temp_rslt);
            colnames = {'mean_ret', 'std_ret', 'median_ret',...
                'skew_ret', 'kurtosis_ret', 'GK_vol', 'max_dd', ...
                'min_turnover_pct', 'med_turnover_pct', 'max_turnover_pct', ...
                'min_turnover_cash', 'med_turnover_cash','max_turnover_cash', ...
                'alpha', 'beta', 'tracking_error'};
            if(nargin == 3)
                rslt = struct();
                for c=1:numel(colnames)
                    %                     rslt = setfield(rslt, colnames{c}, temp_rslt(:,c));
                    rslt.(colnames{c}) = temp_rslt(:,c);
                end
            else
                rslt = temp_rslt;
            end
        end
        
    case 'balance_sheet_cp68'
        sec_id = varargin{1};
        yr = varargin{2};
        quarter = 0;
        if(nargin==4)
            quarter = varargin{3};
        end
        
        temp_sql = ['select * from `balance_sheet_cp68` where security_id = %d AND ' ...
            '`quarter`=%d AND `year` = %d' ];
        sql = sprintf(temp_sql,sec_id,quarter,yr);
        curs = exec(dbConn, sql);
        temp_rslt = get(fetch(curs), 'Data');
        rslt = ' ';
        if ~strcmpi(temp_rslt{1}, 'no data')
            temp_rslt = cell2mat(temp_rslt);
            colnames = {
                'current_assets','cash_cash_equivalent',...
                'short_term_financial_investment','short_term_account_receivables',...
                'inventory','other_current_assets','non_current_assets',...
                'long_term_account_receivables','fixed_asset','goodwill',...
                'real_estate_investment','long_term_financial_investment',...
                'other_long_term_assets','total_assets','liabilities',...
                'short_term_liabilities','long_term_liabilities',...
                'operational_reserves','other_liabilities','equity',...
                'expenditures_other_funds','owners_equity'
                };
            
            rslt = struct();
            for c=1:numel(colnames)
                %                 rslt = setfield(rslt, colnames{c}, temp_rslt(:,c+4));
                rslt.(colnames{c}) = temp_rslt(:,c+4);
            end
        end
    case 'income_statement_cp68'
        sec_id = varargin{1};
        yr = varargin{2};
        quarter = 0;
        if(nargin==4)
            quarter = varargin{3};
        end
        
        temp_sql = ['select * from `income_statement_cp68` where security_id = %d AND ' ...
            '`quarter`=%d AND `year` = %d' ];
        sql = sprintf(temp_sql,sec_id,quarter,yr);
        curs = exec(dbConn, sql);
        temp_rslt = get(fetch(curs), 'Data');
        rslt = ' ';
        if ~strcmpi(temp_rslt{1}, 'no data')
            temp_rslt = cell2mat(temp_rslt);
            colnames = {
                'gross_sale_revenues','deduction_revenues','net_sales',...
                'cost_of_goods_sold','gross_profit','financial_activities_revenues',...
                'financial_expenses','selling_expenses','managing_expenses',...
                'net_profit_from_operating_activities','other_income',...
                'other_expenses','other_profits','total_profit_before_tax',...
                'corporate_income_tax_expenses','profit_after_corporate_income_tax'
                };
            
            rslt = struct();
            for c=1:numel(colnames)
                %                 rslt = setfield(rslt, colnames{c}, temp_rslt(:,c+4));
                rslt.(colnames{c}) = temp_rslt(:,c+4);
            end
        end
        
        
    case 'balance_sheet_stox'
        sec_id = varargin{1};
        yr = varargin{2};
        quarter = 0;
        if(nargin==4)
            quarter = varargin{3};
        end
        
        temp_sql = ['select * from `balance_sheet_stox` where security_id = %d AND ' ...
            '`quarter`=%d AND `year` = %d' ];
        sql = sprintf(temp_sql,sec_id,quarter,yr);
        curs = exec(dbConn, sql);
        temp_rslt = get(fetch(curs), 'Data');
        rslt = ' ';
        if ~strcmpi(temp_rslt{1}, 'no data')
            temp_rslt = cell2mat(temp_rslt(:,2:end));
            colnames = {
                'ts_ngan_han', 'tien_va_td_tien', 'gt_thuan_dau_tu_ngan_han', 'khoan_phai_thu', 'hang_ton_kho', 'ts_luu_dong_khac',...
                'ts_dai_han', 'phai_thu_dai_han', 'ts_co_dinh', 'ts_dau_tu', 'dau_tu_dai_han', 'loi_the_thuong_mai', 'ts_dai_han_khac', 'tong_ts',...
                'no_phai_tra', 'no_ngan_han', 'no_dai_han',...
                'von_CSH', 'von_va_cac_quy', 'quy_khac', 'lai_chua_phan_phoi', 'von_ngan_sach_NH_quy', 'loi_ich_CDTS', 'tong_cong_nguon_von'
                };
            
            rslt = struct();
            for c=1:numel(colnames)
                %                 rslt = setfield(rslt, colnames{c}, temp_rslt(:,c+2));
                rslt.(colnames{c}) = temp_rslt(:,c+2);
            end
        end
    case 'income_statement_stox'
        sec_id = varargin{1};
        yr = varargin{2};
        quarter = 0;
        if(nargin==4)
            quarter = varargin{3};
        end
        
        temp_sql = ['select * from `income_statement_stox` where security_id = %d AND ' ...
            '`quarter`=%d AND `year` = %d' ];
        sql = sprintf(temp_sql,sec_id,quarter,yr);
        curs = exec(dbConn, sql);
        temp_rslt = get(fetch(curs), 'Data');
        rslt = ' ';
        if ~strcmpi(temp_rslt{1}, 'no data')
            %temp_rslt = cell2mat(temp_rslt);
            temp_rslt = cell2mat(temp_rslt(:,2:end));
            colnames = {
                'doanh_thu_thuan', 'gia_von_hang_ban', 'lai_gop',...
                'thu_nhap_tai_chinh', 'chi_phi_tai_chinh', 'chi_phi_tien_lai_vay',...
                'chi_phi_ban_hang', 'chi_phi_quan_ly', 'lai_tu_hdkd', 'lai_cty_ld_lk', 'thu_nhap_khac',...
                'lai_truoc_thue', 'thue_TNDN_ht', 'thue_TNDN_hl', 'lai_sau_thue',...
                'loi_ich_CDTS', 'lai_co_dong_cty_me', 'chi_phi_khau_hao_TSCD'
                };
            
            rslt = struct();
            for c=1:numel(colnames)
                %rslt = setfield(rslt, colnames{c}, temp_rslt(:,c+4));
                %                 rslt = setfield(rslt, colnames{c}, temp_rslt(:,c+2));
                rslt.(colnames{c}) = temp_rslt(:,c+2);
            end
        end
    case 'balance_sheet_bank_stox'
        sec_id = varargin{1};
        yr = varargin{2};
        quarter = 0;
        if(nargin==4)
            quarter = varargin{3};
        end
        
        temp_sql = ['select * from `balance_sheet_bank_stox` where security_id = %d AND ' ...
            '`quarter`=%d AND `year` = %d' ];
        sql = sprintf(temp_sql,sec_id,quarter,yr);
        curs = exec(dbConn, sql);
        temp_rslt = get(fetch(curs), 'Data');
        rslt = ' ';
        if ~strcmpi(temp_rslt{1}, 'no data')
            temp_rslt = cell2mat(temp_rslt(:,2:end));
            colnames = {
                'tong_TS', 'tien_mat_vang_bac_da_quy', 'tien_gui_NHNN', 'tien_gui_cac_tctd_khac ',...
                'tong_ck_kinh_doanh', 'ck_kinh_doanh', 'du_phong_giam_gia_ck_kinh_doanh', 'tc_phai_sinh_va_no_tc_khac_ckkd',...
                'tong_cho_vay_khach_hang', 'cho_vay_khach_hang', 'du_phong_rr_cho_vay_KH',...
                'ck_dau_tu', 'ck_dau_tu_san_sang_de_ban', 'ck_dau_tu_giu_den_dao_han', 'du_phong_giam_gia_ck_dau_tu',...
                'gop_von_dau_tu_dai_han', 'dt_vao_cong_ty_con', 'dt_vao_cong_ty_lien_doanh', 'dt_dai_han_khac', 'du_phong_giam_gia_dt_dai_han',...
                'tai_san_co_dinh', 'tai_san_co_dinh_huu_hinh', 'tai_san_co_dinh_thue_tc', 'tai_san_co_dinh_vo_hinh', 'bat_dong_san_dau_tu', 'tai_san_co_khac',...
                'no_phai_tra_va_von_CSH', 'tong_no_phai_tra', 'cac_khoan_no_cp_va_NHNN', 'tien_gui_vay_tctd_khac', 'tien_gui_cua_KH',...
                'tc_phai_sinh_va_no_tc_khac_no', 'von_tai_tro_utdt_cp_tctd_khac', 'phat_hanh_giay_to_co_gia', 'khoan_no_khac',...
                'von_CSH', 'von_cua_tctd', 'quy_cua_tctd',...
                'chenh_lech_tg_hoi_doai', 'chenh_lech_danh_gia_lai_TS', 'loi_nhuan_chua_phan_phoi', 'loi_ich_cua_CDTS'
                };
            
            rslt = struct();
            for c=1:numel(colnames)
                %                 rslt = setfield(rslt, colnames{c}, temp_rslt(:,c+2));
                rslt.(colnames{c}) = temp_rslt(:,c+2);
            end
        end
    case 'income_statement_bank_stox'
        sec_id = varargin{1};
        yr = varargin{2};
        quarter = 0;
        if(nargin==4)
            quarter = varargin{3};
        end
        
        temp_sql = ['select * from `income_statement_bank_stox` where security_id = %d AND ' ...
            '`quarter`=%d AND `year` = %d' ];
        sql = sprintf(temp_sql,sec_id,quarter,yr);
        curs = exec(dbConn, sql);
        temp_rslt = get(fetch(curs), 'Data');
        rslt = ' ';
        if ~strcmpi(temp_rslt{1}, 'no data')
            temp_rslt = cell2mat(temp_rslt(:,2:end));
            colnames = {
                'thu_nhap_lai_va_khoan_tt','chi_phi_lai','thu_nhap_lai_thuan',...
                'thu_nhap_hd_dich_vu', 'chi_phi_hd_dich_vu', 'lai_thuan_hd_dich_vu',...
                'kd_ngoai_hoi_vang', 'chung_khoan_KD', 'chung_khoan_dau_tu', 'hoat_dong_khac',...
                'thu_nhap_gop_von_CP', 'tong_thu_nhap_HD', 'tong_chi_phi_HD',...
                'LN_HDKD_truoc_CF_du_phong', 'chi_phi_du_phong_RR', 'loi_nhuan_tt',...
                'thue_TNDN', 'co_dong_thieu_so', 'loi_nhuan_thuan'
                };
            
            rslt = struct();
            for c=1:numel(colnames)
                %                 rslt = setfield(rslt, colnames{c}, temp_rslt(:,c+2));
                rslt.(colnames{c}) = temp_rslt(:,c+2);
            end
        end
    case 'financial_indicators'
        security_id = varargin{1};
        security = get_info_from_database('security-info',security_id);
        if(~isstruct(security))
            rslt = [];
            return;
        end
        switch(security.sector_id)
            case 12 %bank
                table = 'financial_indicators_bank';
            case 2 %insurance
                table = 'financial_indicators_insurance';
            case 6 %financial
                table = 'financial_indicators_financial';
            case 0 
                fprintf('Can not find sector');
                rslt = [];
                return;
            otherwise
                table = 'financial_indicators';
        end

        if(nargin == 2)
            yr = year(now);
            qr = floor(month(now) / 3);
            if(qr==0)
                yr = yr - 1;
                qr = 4;
            end
            sql = sprintf(['select * from `%s` where security_id = %d '...
            'AND `year` = %d AND `quarter`=%d' ]...
            ,table,security_id,yr,qr);
        elseif(nargin==4)
            yr = varargin{2};
            qr = varargin{3};
            sql = sprintf(['select * from `%s` where security_id = %d '...
            'AND `year` = %d AND `quarter`=%d' ]...
            ,table,security_id,yr,qr);
        else
            yr1 = varargin{3};
            yr2 = varargin{5};
            if(yr1 > yr2)
                fpritnf('Begin time bigger than end time');
                rslt = [];
                return;
            end
            if((yr1 > 10000) || (yr2 > 10000))                                 
                sql = sprintf(['select * from `%s` where security_id = ''%d'' '...
                'AND (`year`*10+`quarter`) >= %d AND (`year`*10+`quarter`) <= %d '...
                'AND `quarter` NOT IN ( 0, 5 ) '...
                'ORDER BY `year` ASC,`quarter` ASC']...
                ,table,security_id,yr1,yr2);
            else
                sql = sprintf(['select * from `%s` where security_id = ''%d'' '...
                'AND `year` >= %d AND `year` <= %d AND `quarter` NOT IN ( 0, 5 ) '...
                'ORDER BY `year` ASC,`quarter` ASC']...
                ,table,security_id,yr1,yr2);
            end
        end
        
        
        curs = fetch(exec(dbConn, sql));
        temp_rslt = get(curs, 'Data');
        
        if(rows(curs))
            if(nargin==6)
                rslt = temp_rslt;                
            else
                rslt = struct();
                columns = get_info_from_database('column',table);
                for i = 1 : numel(columns)
                    rslt.(columns{i}) = temp_rslt{i};
                end
            end
            
        end
        
    case 'financial_indicator_stox'
        sec_id = varargin{1};
        yr = varargin{2};
        temp_sql = ['select * from `financial_ratio_stox` '...
            'where security_id = %d AND `year` = %d' ];
        sql = sprintf(temp_sql,sec_id,yr);
        curs = exec(dbConn, sql);
        temp_rslt = get(fetch(curs), 'Data');
        rslt = ' ';
        if ~strcmpi(temp_rslt{1}, 'no data')
            temp_rslt = cell2mat(temp_rslt);
            colnames = {
                'gia', 'sl_CPLH', 'von_hoa', 'co_tuc', 'eps', 'pe', 'bvs', 'pb',...
                'ts_loi_nhuan_gop', 'ts_loi_nhuan_truoc_thue', 'ts_loi_nhuan_rong',...
                'ebit', 'ebitda', 'roe', 'roa',...
                'vong_quay_hang_ton_kho', 'vong_quay_khoan_phai_thu', 'vong_quay_khoan_phai_tra', 'vong_quay_tong_tai_san',...
                'kha_nang_tra_lai_vay', 'no_t_tong_tai_san', 'no_t_von_CSH', 'vay_dai_han_t_tong_ts', 'vay_dai_han_t_von_CSH',...
                'no_ngan_han_t_tong_TS', 'no_ngan_han_t_von_CSH',...
                'tang_truong_doanh_thu', 'tang_truong_LN_rong',...
                'ts_thanh_toan_hien_hanh', 'ts_thanh_toan_nhanh', 'ts_thanh_toan_tien_mat'
                };
            
            rslt = struct();
            for c=1:numel(colnames)
                %                 rslt = setfield(rslt, colnames{c}, temp_rslt(:,c+3));
                rslt.(colnames{c}) = temp_rslt(:,c+3);
            end
        end
        
    case 'financial_indicator_bank_stox'
        sec_id = varargin{1};
        yr = varargin{2};
        temp_sql = ['select * from `financial_ratio_bank_stox` '...
            'where security_id = %d AND `year` = %d' ];
        sql = sprintf(temp_sql,sec_id,yr);
        curs = exec(dbConn, sql);
        temp_rslt = get(fetch(curs), 'Data');
        rslt = ' ';
        if ~strcmpi(temp_rslt{1}, 'no data')
            temp_rslt = cell2mat(temp_rslt);
            colnames = {
                'gia', 'sl_CPLH', 'von_hoa', 'co_tuc', 'eps', 'pe', 'bvs', 'pb',...
                'ts_loi_nhuan_rong', 'ts_sinh_lai', 'no_chiu_lai', 'chi_phi_t_tren_TN_hoat_dong',...
                'ts_sinh_loi_cua_TS', 'chi_phi_von', 'NIS', 'NIM', 'roa', 'roe', 'roa_tdp',...
                'tang_truong_LN_rong', 'tang_truong_tong_TS', 'tang_truong_cho_vay', 'tang_truong_huy_dong',...
                'tang_truong_chi_phi_hoat_dong', 'tang_truong_LN_truoc_du_phong', 'tang_truong_TN_lai_thuan', 'tang_truong_TN_ngoai_lai',...
                'don_bay_tai_chinh', 'cho_vay_t_huy_dong', 'no_xau', 'no_xau_t_cho_vay', 'du_phong_t_no_xau', 'chi_phi_du_phong_t_cho_vay'
                };
            
            rslt = struct();
            for c=1:numel(colnames)
                %                 rslt = setfield(rslt, colnames{c}, temp_rslt(:,c+3));
                rslt.(colnames{c}) = temp_rslt(:,c+3);
            end
        end
        
    case 'liquidity_measures'
        date_from_val = datenum(varargin{4}, 'dd/mm/yyyy');
        date_to_val   = datenum(varargin{6}, 'dd/mm/yyyy');
        
        year_from = year(date_from_val);
        year_to   = year(date_to_val);
        
        end_of_year = arrayfun(@(y)eomdate(y,12), year_from:year_to);
        nb_years = numel(end_of_year);
        if nb_years == 1
            date_to_val = date_to_val + 1;
            date_val    = [date_from_val; date_to_val];
        else
            date_val = NaN(2*nb_years,1);
            date_val(1:2:end) = [date_from_val end_of_year(1:end-1)+1];
            date_val(2:2:end) = [end_of_year(1:end-1)+1 date_to_val];
        end
        tmp_rslt = [];
        for y=1:nb_years
            table_name = sprintf('liquidity_measures_%d',year_from+y-1);
            date_from = datestr(date_val(2*y-1), 'yyyy-mm-dd');
            date_to   = datestr(date_val(2*y), 'yyyy-mm-dd');
            
            req_sql = sprintf(['SELECT * ', ...
                sprintf('FROM %s ', table_name), ...
                'WHERE security_id = %d ', ...
                'AND date BETWEEN ''%s'' and ''%s'''], varargin{2}, date_from, date_to);
            curs = exec(dbConn, req_sql);
            temp_rslt = get(fetch(curs), 'Data');
            if ~(isnumeric(temp_rslt) || strcmpi(temp_rslt{1}, 'no data'))
                dates = cellfun(@(c)datenum(c(1:19), 'yyyy-mm-dd HH:MM:SS'), temp_rslt(:,3));
                tmp_rslt = vertcat(tmp_rslt,horzcat(dates, cell2mat(temp_rslt(:,4:end))));
            end
        end
        if ~isempty(tmp_rslt)
            curs = exec(dbConn, sprintf('SHOW FIELDS FROM %s', table_name));
            colnames = get(fetch(curs), 'Data');
            rslt = struct();
            for i=1:size(tmp_rslt, 2)
                %                 rslt = setfield(rslt, colnames{i+2}, tmp_rslt(:,i));
                rslt.(colnames{i+2}) = tmp_rslt(:,i);
            end
        else
            rslt = [];
        end
        
    case 'financial-report-item-id'
        if nargin == 2
            viet_name = varargin{1};
            eng_name = varargin{1};
        elseif nargin == 3
            viet_name = varargin{1};
            eng_name = varargin{2};
        else
            fprintf('get_info_from_database:mode <%s> requires at least 1 parameter.\n', mode);
        end
        sql_template = 'SELECT item_id FROM financial_report_items WHERE viet_name LIKE ''%%%s%%'' OR eng_name LIKE ''%%%s%%''';
        curs = exec(dbConn, sprintf(sql_template, viet_name, eng_name));
        temp_rslt = get(fetch(curs), 'Data');
        if strcmpi(temp_rslt{1}, 'no data')
            rslt = [];
        else
            rslt = temp_rslt{1};
        end
        
    case 'capital'
        security_id = varargin{1};
        if ischar(security_id)
            security_id = get_info_from_database('sec_id', security_id);
            if ischar(security_id)
                fprintf('get_info_from_database:mode <%s>: cannot find security_id of %s',...
                    mode, varargin{1});
                return
            end
        end
        try
            last_price = get_info_from_database('last_price', 'sec_id', security_id);
            floating = get_info_from_database('floating', security_id);
            rslt = last_price{2} * floating;
        catch err
            rslt = [];
            fprintf(['get_info_from_database:mode <%s> '...
                'cannot get capital for this security_id.\n  %s\n'], mode,err.identifier);
        end
        
    
    case 'sql'
        
        req_sql = varargin{1};
        if(strcmpi(req_sql,'commit'))
            fprintf('Commit now ^_^\n');
            dbConn.commit();
            return
        end
        curs = exec(dbConn, req_sql);
        if(isempty(curs.Message))
            rslt = get(fetch(curs), 'Data');
        else
            fprintf('Error %s',curs.Message);
            rslt = 'No Data';
        end
        
        
    otherwise
        fprintf('get_info_from_database:mode <%s> is not recognized.\n', mode);
end
end